What is Edition Based Redefinition (EBR) in Oracle?

Edition Based Redefinition (EBR) is a new feature introduced from Oracle Software Version 11g R2.Edition-based redefinition allows multiple versions of PL/SQL objects, views and synonyms in a single schema.This feature facilitates Online patching of database objects in R12.2 online Patching.

Editionable and Non-editionable Objects

Below schema object types are editionable in the database:

SYNONYM

VIEW

SQL translation profile

All PL/SQL object types:

FUNCTION

LIBRARY

PACKAGE and PACKAGE BODY

PROCEDURE

TRIGGER

TYPE and TYPE BODY

If a schema object type is editionable in the database, then it can be editionable in schemas.All other schema object types are noneditionable in the database and in every schema, and objects of that type are always noneditioned. Tables are always noneditioned objects.

To view the editions related information in the database, use the below datadictionary tables.

DBA_EDITIONS: SELECT * FROM dba_editions;

DBA_OBJECTS_AE: DBA_OBJECTS_AE view shows all objects, of all editions.

What is the use of MMON background process in Oracle Database?

MMON (Manageability Monitor)

MMON (Manageability Monitor) is a background process introduced in Oracle 10g. It gathers memory statistics (snapshots) and stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds.

How to Compile Forms in Oracle Applications Release 11i and Release 12?

Steps to compile forms  in  Oracle Applications Release 12.1.x and 12.2.x

Login to Application Tier node as applmgr user

Change to $AU_TOP/forms/US

Execute the Below command to generate form files.

frmcmp_batch userid=apps/<password> module=formname.fmb
output_file=$PROD_TOP/forms/US/<name>.fmx module_type=form batch=no compile_all=special

After execution of the above command, verify there are no compilation errors in output displayed and the fmx is created succesfully.


Steps to compile forms  in  Oracle Applications Release 11i

Login to Application Tier node as applmgr user

Change to $AU_TOP/forms/US

Execute the Below command to generate form files.

f60gen module=$AU_TOP/forms/US/<custom_form>.fmb  userid=apps/<passwd> output_file=$PRODUCT_TOP/forms/<customformname>.fmx module_type=form compile_all=yes


Tips:

Use command "f60gen help=y" or "frmcmp_batch.sh help=y" to find the optional parameters.

Using Compile_all= yes changes cached version in the source file in addition to compiling pl/sql in the utput fmx file.

Oracle EBS DBA Utilities :FNDLOAD

What are the objects loaded using FNDLOAD in Oracle Applications?

FNDLOAD (Generic Loader) is a concurrent Program which is used to move metadata between database and text file representations.The loader reads a configuration file to determine what data to access.

FNDLOAD can download data from an application entity into a portable, editable text file. This file can then be uploaded into any other database to copy the data. Conversion between database store and file format is specified by a configuration file that is read by the loader. FNDLOAD downloads data from a database according to a configuration (.lct) file, and converts the data into a data file (.ldt file). FNDLOAD can then upload this data to another database using a configuration file.

The loader operates in one of two modes: download or upload. In the download mode, data is downloaded from the database to a text file; in the upload mode, data is uploaded from a text file to the database.

Syntax to upload/download concurrent Programs using FNDLOAD:

To Download:

FNDLOAD apps/** 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct  PROGNAME.ldt PROGRAM APPLICATION_SHORT_NAME="APPLICATION_SHORT_NAME CONCURRENT_PROGRAM_NAME="Program_Name"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct PROGNAME.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Below are the application entities which can be uploaded/downloaded using FNDLOAD.

FND Messages

Lookups

EBS Front End Users

Responsibilities

Alerts

Concurrent Programs

Profile Options

Request Groups 

Menus

What are the differences between Oracle Applications R12.1.x and R12.2.x

From DBA perspective, below are the changes in R12.2.x version of Oracle Applications, compared to R12.1.3.

1. Dual FS: Oracle Application Release 12.2 has dual filesystems(RUN FS and PATCH FS) to facilitate Online Patching.This Requires double the mountpoint space at MiddleTier Level.

2. Weblogic Server: Oracle HTTP Server in R12.1 is replaced by Oracle Weblogic Server.

10.1.3 Home is replaced by FMW_HOME.Weblogic Server Manages all oacore, forms and oafm servers in R12.2.

3. Online Patching : Dual Filesystem and EBR feature in 11gR2 Database facilitate online patching. Online Patching minimizes the downtime required for patching.adpatch utility in R12.1.x is replaced by adop utility. adop cycle runs in 5 Phases : prepare,apply,finalize,cutover and cleanup. adop internally calls adpatch utility to run the driver files associated with adop phases.

4. APPS Password Change: After Changing Apps Password in R12.2, we need to update the new password in EBS Datasource used by Weblogic Domain.To know more about apps password change in Oracle applications R12.2, check the below link.

http://www.appsdbadiaries.com/2016/02/changing-apps-password-in-r122.html

5. Cloning : While Cloning of R12.2.x adcfgclone instance needs to be run twice,  on RUN fs and PATCH fs.From 12.2.5 Version (OR R12.AD-TXK Delta 7), we have the dualfs option which creates both run fs and patch fs during clone process.

What are Nodeapps Services in Oracle RAC

Nodeapps are standard set of oracle application services which are started automatically for RAC.

Node apps Include:

1) VIP.
2) Oracle Net listener.
3) Global Service Daemon.
4) Oracle Notification Service.

Nodeapp Services run on each node of the cluster and will switched over to other nodes through VIP during the failover.


Useful commands to maintain nodeapps services:

srvctl stop nodeapps -n NODE1 
[ STOP NODEAPPS  on NODE 1 ]
srvctl stop nodeapps -n NODE2            
[ STOP NODEAPPS  on NODE 2 ]

srvctl start nodeapps -n NODE1              
[ START NODEAPPS on NODE1  ]
srvctl start nodeapps -n NODE2             
[  START NODEAPPS ON NODE2 ]

srvctl status nodeapps                      
[Check the status of services on all nodes]

Oracle Database Administrator Interview Questions on RAC

Oracle RAC (Voting Disk ) FAQ's

1.What is Voting Disk?

Voting Disk is a file that maintains node membership details.
All members in the Cluster read and write Heartbeat Information in Voting Disk.

2. What Information is stored in Voting Disk?

Voting disks contain static and dynamic data.
Static data : Info about nodes in the cluster
Dynamic data : Disk heartbeat logging
It maintains and consists of important details about the cluster nodes membership, such as
- which node is part of the cluster,
- which node is joining the cluster, and
- which node is leaving the cluster.

3. Why do we need Voting Disk?

CSSD processes (Cluster Services Synchronization Daemon) monitor the health of  RAC nodes employing two distinct heart 

beats: Network heart beat and Disk heart beat. Healthy nodes will have continuous network and disk heartbeats exchanged 

between the  nodes. Break in heart beat indicates a possible error scenario. There are few different scenarios possible 

with missing heart beats:
1. Network heart beat is successful, but disk heart beat is missed.
2. Disk heart beat is successful, but network heart beat is missed.
3. Both heart beats failed.
In addition, with numerous nodes, there are other possible scenarios too. Few possible scenarios:
1. Nodes have split in to N sets of nodes, communicating within the set, but not with members in other set.
2. Just one node is unhealthy.
Nodes with quorum will maintain active membership of the cluster and other node(s) will be fenced/rebooted.

4.Why do we have odd number of voting disks?

Odd number of Voting disks are required to prevent Split Brain Syndrome.
A node must be able to access more than half of the voting disks in order to decide which node can be evicted incase of 

failure.

5. How to backup Voting Disk?

Prior to Oracle 11g R2 version, Voting Disk is backed up using dd command

From 11g R2, no need to manually backup voting disk.
It is automatically backup along with OCR  whenever there is a configuration change.

In 11g R2, Restoring Voting disk from a manually copied backupfile may prevent cluster services from starting up.

Oracle Database Administrator Interview Questions on RMAN

RMAN INTERVIEW QUESTIONS
========================

1.What is the difference between recovery catalog and Nocatalog backup in RMAN?

NoCatalog Backup:

ByDefault RMAN connects to the target database in Nocatalog Mode.
In Nocatalog Mode, Backup Information and Metadata related to RMAN is stored in target database controlfile.

Catalog Backup:

A recovery catalog is a schema created in a separate database that contains metadata obtained from the target control file.
In recovery catalog we can store rman scripts.

We can store metadata about multiple incarnations of a single target database in the catalog.
Recovery catalog is central and can have information of many databases.
If the control file is lost and must be restored from backup, the backup configuration information is available when the database is not mounted.

2.What are the differences between crosscheck and validate commands?

Validate command is to examine a backup set and report whether it can be restored. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksum to verify that the contents are intact so that backup can be successfully restored if necessary.

Crosscheck command is to verify the status of backups and copies recorded in the RMAN repository against media such as disk or tape. The crosscheck command only processes files created on the same device type as the channel running crosscheck.

3.What is obsolete backup & expired backup?

A status of “expired” means that the backup piece or backup set is not found in the backup destination.

A status of “obsolete” means the backup piece is still available, but it is no longer needed. The backup piece is no longer needed since RMAN has been configured to no longer need this piece after so many days have elapsed, or so many backups have been performed.

4.What is the difference between hot backup & RMAN backup?

We need to put the database in Backup mode for Hot  Backup. RMAN Backup does not need the database to be in Backup mode.

5. Which Tables are have information required for RMAN list & report commands ?
 V$BACKUP_FILES and recovery catalog views e.g.,  RC_DATAFILE_COPY or RC_ARCHIVED_LOG.

Below tables contain RMAN Catalog information:

RC_DATABASE_INCARNATION 
RC_BACKUP_COPY_DETAILS
RC_BACKUP_CORRUPTION
RC_BACKUP-DATAFILE_SUMMARY

What is the difference between dbtechstack,dbtier and dbconfig modes in Cloning?

While Cloning Oracle Applications, adcgclone can be executed in 3 Modes on Database node.


1)perl adcfgclone.pl dbTier

 It configures the ORACLE_HOME on the target database node and  recreate the controlfiles.
 This is used in case of standby database/hot backups.   


2)perl adcfgclone.pl dbTechStack 

It configures the ORACLE_HOME on the target database tier node only. Relink the oracle home.

The below steps has to be performed manually
1. Create the Target Database control files.
2. Start the Target System Database in open mode
3. Run the library update script against the Database
cd $RDBMS_ORACLE_HOME/appsutil/install/[CONTEXT NAME]
sqlplus "/ as sysdba" @adupdlib.sql [libext]
 Where [libext] should be set to 'sl' for HP-UX, 'so' for any other UNIX platform, 
or 'dll' for Windows.

3)perl adcfgclone.pl dbconfig

It configures the database with context file. Database must be open while running adcfgclone in dbconfig mode.
e.g 
cd $RDBMS_ORACLE_HOME/appsutil/clone/bin
perl adcfgclone.pl dbconfig <Context_file>

Where Target Context File is:
$RDBMS_ORACLE_HOME/appsutil/<SID_HOSTNAME>.xml

Fusion Middleware FAQ's

1.What is the use of nodemanager in Weblogic?

Nodemanager enables us to startup/shutdown/maintain Managed Servers from console.
It is a Java utility that runs as separate process from WebLogic Server.
It is recommended to enable nodemanager in Clustered domains & High Availability Environments.

• Monitors server availability and can restart failed servers
• Can be used to migrate servers on a failed machine to Can be used to migrate servers on a failed machine to another machine

2.How to Check if nodemanager is running?

$ps -ef|grep -i nodemanager


3.How to Start/Stop Nodemanager?

Use the script to startup Nodemanager located in $WL_HOME\server\bin

$./startNode Manager.sh

or

$nohup ./startNodeManager.sh


To stop nodemanager process, get the process id using the below command

$ps -ef|grep -i nodemanager

Kill the java process related to nodemanager at OS level

or Simple close the command shell in which nodemanager is started

How to Put Concurrent Requests in Hold Status?

During some scheduled maintenance activities, oracle apps database administrator would require to keep the Pending Jobs on Hold before bounce and release them after the scheduled activity.
This process help to bring down Concurrent Manager quickly and the pending jobs are preserved for running after the maintenance is complete.


1) Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
2) select count(*) from apps.conc_req_on_hold
3) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.conc_req_on_hold);

NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same

4) Commit;

To Release hold on Concurrent Requests patching, run the below sql :

5) update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.conc_req_on_hold);

6)Commit the changes

 commit;

Oracle Applications Database Administrator Interview Questions

1. How to verify if a patch is applied or not (11i and R12)

A: Query from ad_bugs table.(11i and r12)
select bug_number,created.last_update_date from ad_bugs where bug_number='&patchnum';

R12.2

select ad_patch.is_patch_applied('R12',-1,20034256) from dual;

expected results:
EXPLICIT = applied
NOT APPLIED = not applied / aborted

2.What is the difference between ad_bugs and ad_applied_patches?

A: If a patch is applying multiple bug fixes, the details of all bugs fixed by the patch can be found from ad_bugs,
ad_applied_patches has information only about patches applied using adpatch.


Two tables to check if the patch is applied or not:

This table includes the defined bugs on the system: 
SELECT   bug_number 
FROM     apps.ad_bugs
WHERE   bug_number LIKE '%'&patchnum'%';

This table includes patches applied on the system:
SELECT patch_name 
FROM   apps.ad_applied_patches
WHERE patch_name LIKE '%'&patchnum'%'

3. How to check if a forms patch is applied?

A: There is no specific way to check if a forms patch is applied. Forms Patches are usually applied through Shell scripts.